import time

import pymysql
import redis
import json

db = pymysql.connect(
    host='localhost',
    user='root',
    password='root',
    database='clouds'
)

cursor = db.cursor()

db2 = pymysql.connect(
    host='localhost',
    user='root',
    password='root',
    database='django003'
)

cursor2 = db2.cursor()

r = redis.Redis(
    host='localhost',
    port=6379
)

columns = ['dev_id', 'temp', 'hum', 'create_time']

def selectAll():
    db = pymysql.connect(
        host='localhost',
        user='root',
        password='root',
        database='clouds'
    )

    cursor = db.cursor()

    sql = 'select * from tb_temp_hum'
    cursor.execute(sql)

    results = cursor.fetchall()
    cursor.close()

    return results

while True:
    i = 0
    results = selectAll()

    for result in results:
        data = dict(zip(columns, result[1:]))
        data['create_time'] = data['create_time'].strftime('%Y-%m-%d %H:%M:%S')
        data = json.dumps(data)
        r.set(result[0], data)

    keys = r.keys('*')
    keys = sorted(keys)
    sql2 = 'insert into tb_temp_hum(dev_id, temp, hum, create_time) values (%s, %s, %s, %s)'

    for key in keys:
        key = key.decode('utf-8')
        result = r.get(key)
        result = json.loads(result)
        data = list(result.values())
        cursor2.execute('select * from tb_temp_hum where id=%s', key)
        existing_data = cursor2.fetchone()
        if existing_data is None:
            cursor2.execute(sql2, data)
            db2.commit()
            i += 1
    print(f'同步成功{i}条数据！')

    time.sleep(10)

cursor2.close()
db2.close()
db.close()